use rusqlite::{params, Connection, Result};
use crate::db::models::{Notice, Target, Goods, Canton};

/// 数据库操作模块
/// 
/// 提供数据库连接管理和表操作功能，包括创建表、插入和更新数据等操作。

/**
 * 创建数据库连接并初始化表结构
 * 
 * 如果数据库文件不存在则自动创建，并创建三个主要表：
 * - notice_table: 存储公告信息
 * - target_table: 存储交易信息
 * - goods_table: 存储宗地信息
 * 
 * @return 成功时返回数据库连接对象，失败时返回错误
 */
pub fn create_db() -> Result<Connection> {
    // 连接到SQLite数据库（如果不存在，则创建）
    let conn = Connection::open("jntujy.db")?;

    // 启用外键约束（SQLite默认禁用）
    conn.execute("PRAGMA foreign_keys = ON", [])?;

    // 创建notice_table表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS notice_table (
            notice_id TEXT PRIMARY KEY,  -- 公告ID
            notice_date TEXT,            -- 公告日期
            area TEXT,                   -- 区县
            identifier TEXT,             -- 公告文号
            notice_name TEXT             -- 公告名称
        )",
        [],
    )?;
    
    // 创建target_table表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS target_table (
            target_id TEXT PRIMARY KEY,  -- 交易ID
            notice_id TEXT NOT NULL,     -- 公告ID,对应notice_table中的notice_id
            status INTEGER,              -- 交易状态
            is_stop INTEGER,             -- 是否停止交易
            trans_bidder TEXT,           -- 竞得人
            end_trans_time TEXT,         -- 交易结束时间
            trans_price REAL,            -- 交易总成交价
            trans_price_above REAL,      -- 交易地上成交价
            trans_price_under REAL,      -- 交易地下成交价
            price_step REAL,             -- 增加幅度
            begin_price REAL,            -- 交易总起拍价
            begin_price_above REAL,      -- 交易地上起拍价
            begin_price_under REAL,      -- 交易地下起拍价
            notice_xh TEXT,              -- 公告序号
            begin_focus_time TEXT,       -- 挂牌开始时间
            end_focus_time TEXT,         -- 挂牌结束时间
            begin_apply_time TEXT,       -- 申请开始时间
            end_apply_time TEXT          -- 申请结束时间
        )",
        [],
    )?;
    
    // 创建goods_table表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS goods_table (
            goods_id TEXT PRIMARY KEY,      -- 宗地ID
            target_id TEXT NOT NULL,        -- 交易ID,对应target_table中的target_id
            business_name TEXT,             -- 交易类别
            goods_no TEXT,                  -- 宗地编号
            goods_name TEXT,                -- 宗地名称
            land_area REAL,                 -- 宗地面积
            goods_use TEXT,                 -- 土地用途
            use_years TEXT,                 -- 出让年期
            address TEXT,                   -- 位置
            goods_begin_price REAL,         -- 宗地总起拍价
            goods_begin_price_above REAL,   -- 宗地地上起拍价
            goods_begin_price_under REAL,   -- 宗地地下起拍价
            trans_goods_price REAL,         -- 交易总成交价
            trans_goods_price_above REAL,   -- 交易地上成交价
            trans_goods_price_under REAL,   -- 交易地下成交价
            plot1_down TEXT,                -- 地上容积率下限
            plot1_flag TEXT,                -- 地上容积率下限标志
            plot1_up TEXT,                  -- 地上容积率上限
            plot1_flag2 TEXT,               -- 地上容积率上限标志
            plot2_down TEXT,                -- 地下容积率下限
            plot2_flag TEXT,                -- 地下容积率下限标志
            plot2_up TEXT,                  -- 地下容积率上限
            plot2_flag2 TEXT,               -- 地下容积率上限标志
            build_density_down TEXT,        -- 建筑密度下限
            build_density_flag TEXT,        -- 建筑密度下限标志
            build_density_up TEXT,          -- 建筑密度上限
            build_density_flag2 TEXT,       -- 建筑密度上限标志
            ild_height TEXT,                -- 建筑高度
            green_ratio_down TEXT,          -- 绿化率下限
            green_ratio_flag TEXT,          -- 绿化率下限标志
            green_ratio_up TEXT,            -- 绿化率上限
            green_ratio_flag2 TEXT,         -- 绿化率上限标志
            remark TEXT,                    -- 其他说明
            other_condition TEXT,           -- 其他条件
            start_required TEXT,            -- 必要条件
            deposit_price TEXT,             -- 保证金
            price_step REAL,                -- 加价幅度
            crj_yjk REAL,                   -- 出让金预交款
            allow_union INTEGER,            -- 是否允许联合竞买
            percent_price REAL,             -- 本宗地占交易价格比例
            develop TEXT                    -- 开发强度
        )",
        [],
    )?;
    
    // 创建canton_table表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS canton_table (
            id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 无符号整型自增主键
            canton_id TEXT NOT NULL,               -- 区县ID
            canton_name TEXT,                      -- 区县名称
            organ_id TEXT,                         -- 机构ID
            orgen_name TEXT                        -- 机构名称
        )",
        [],
    )?;
    
    // 为canton_id字段添加索引
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_canton_id ON canton_table(canton_id)",
        [],
    )?;
    
    Ok(conn)
}
/**
 * 插入公告信息到数据库
 * 
 * 使用INSERT OR IGNORE确保当存在相同notice_id时不重复插入
 * 
 * @param conn 数据库连接
 * @param notice 公告信息对象
 * @return 成功时返回Ok(()), 失败时返回错误
 */
pub fn insert_notice(conn: &Connection, notice: &Notice) -> Result<()> {
    conn.execute(
        "INSERT OR IGNORE INTO notice_table (notice_id, notice_date, area, identifier, notice_name)
         VALUES (?1, ?2, ?3, ?4, ?5)",
        params![notice.notice_id, notice.notice_date, notice.area, notice.identifier, notice.notice_name],
    )?;
    Ok(())
}

/**
 * 插入或更新交易信息
 * 
 * 根据target_id判断是否已存在记录：
 * - 不存在则插入完整的新记录
 * - 已存在且状态或停止标志有变化时，更新部分字段
 * 
 * @param conn 数据库连接
 * @param target 交易信息对象
 * @return 成功时返回Ok(()), 失败时返回错误
 */
pub fn insert_or_update_target(conn: &Connection, target: &Target) -> Result<()> {
    // 检查是否存在该target_id
    let mut stmt = conn.prepare("SELECT status, is_stop FROM target_table WHERE target_id = ?")?;
    match stmt.query_row([&target.target_id], |row| {
        let db_status: Option<i32> = row.get(0)?;
        let db_is_stop: Option<i32> = row.get(1)?;
        Ok((db_status, db_is_stop))
    }) {
        Ok((db_status, db_is_stop)) => {
            // 如果status或is_stop不同，则更新数据
            if db_status != target.status || db_is_stop != target.is_stop {
                conn.execute(
                    "UPDATE target_table SET 
                        notice_id = ?,
                        status = ?,
                        is_stop = ?,
                        trans_bidder = ?,
                        end_trans_time = ?,
                        trans_price = ?,
                        trans_price_above = ?,
                        trans_price_under = ?
                     WHERE target_id = ?",
                    params![
                        &target.notice_id,
                        target.status,
                        target.is_stop,
                        target.trans_bidder,
                        target.end_trans_time,
                        target.trans_price,
                        target.trans_price_above,
                        target.trans_price_under,
                        &target.target_id
                    ],
                )?;
            }
        },
        Err(_) => {
            // 不存在则插入新数据
            conn.execute(
                "INSERT INTO target_table (
                    target_id, notice_id, status, is_stop, trans_bidder, end_trans_time,
                    trans_price, trans_price_above, trans_price_under, price_step, begin_price,
                    begin_price_above, begin_price_under, notice_xh, begin_focus_time,
                    end_focus_time, begin_apply_time, end_apply_time
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18)",
                params![
                    &target.target_id,
                    &target.notice_id,
                    target.status,
                    target.is_stop,
                    target.trans_bidder,
                    target.end_trans_time,
                    target.trans_price,
                    target.trans_price_above,
                    target.trans_price_under,
                    target.price_step,
                    target.begin_price,
                    target.begin_price_above,
                    target.begin_price_under,
                    target.notice_xh,
                    target.begin_focus_time,
                    target.end_focus_time,
                    target.begin_apply_time,
                    target.end_apply_time
                ],
            )?;
        }
    }
    Ok(())
}

/**
 * 插入或更新宗地信息
 * 
 * 根据goods_id判断是否已存在记录：
 * - 不存在则插入完整的新记录
 * - 已存在且成交价格有变化时，更新价格相关字段
 * 
 * @param conn 数据库连接
 * @param goods 宗地信息对象
 * @return 成功时返回Ok(()), 失败时返回错误
 */
pub fn insert_or_update_goods(conn: &Connection, goods: &Goods) -> Result<()> {
    // 检查goods_id是否已存在，并获取当前的trans_goods_price
    let mut goods_stmt = conn.prepare("SELECT trans_goods_price FROM goods_table WHERE goods_id = ?")?;
    match goods_stmt.query_row([&goods.goods_id], |row| {
        row.get::<_, Option<f32>>(0)
    }) {
        Ok(db_trans_price) => {
            // 已存在，检查trans_goods_price是否不同
            if db_trans_price != goods.trans_goods_price {
                // 如果价格不同，则更新价格信息
                conn.execute(
                    "UPDATE goods_table SET 
                        trans_goods_price = ?,
                        trans_goods_price_above = ?,
                        trans_goods_price_under = ?
                     WHERE goods_id = ?",
                    params![
                        goods.trans_goods_price,
                        goods.trans_goods_price_above,
                        goods.trans_goods_price_under,
                        &goods.goods_id
                    ],
                )?;
            }
        },
        Err(_) => {
            // 不存在则插入新数据
            conn.execute(
                "INSERT INTO goods_table (
                    goods_id, target_id, business_name, goods_no, goods_name, land_area, goods_use,
                    use_years, address, goods_begin_price, goods_begin_price_above, goods_begin_price_under,
                    trans_goods_price, trans_goods_price_above, trans_goods_price_under, plot1_down,
                    plot1_flag, plot1_up, plot1_flag2, plot2_down, plot2_flag, plot2_up,
                    plot2_flag2, build_density_down, build_density_flag, build_density_up,
                    build_density_flag2, ild_height, green_ratio_down, green_ratio_flag, green_ratio_up,
                    green_ratio_flag2, remark, other_condition, start_required, deposit_price,
                    price_step, crj_yjk, allow_union, percent_price, develop
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16,
                         ?17, ?18, ?19, ?20, ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30,
                         ?31, ?32, ?33, ?34, ?35, ?36, ?37, ?38, ?39, ?40, ?41)",
                params![
                    &goods.goods_id,
                    &goods.target_id,
                    goods.business_name,
                    goods.goods_no,
                    goods.goods_name,
                    goods.land_area,
                    goods.goods_use,
                    goods.use_years,
                    goods.address,
                    goods.goods_begin_price,
                    goods.goods_begin_price_above,
                    goods.goods_begin_price_under,
                    goods.trans_goods_price,
                    goods.trans_goods_price_above,
                    goods.trans_goods_price_under,
                    goods.plot1_down,
                    goods.plot1_flag,
                    goods.plot1_up,
                    goods.plot1_flag2,
                    goods.plot2_down,
                    goods.plot2_flag,
                    goods.plot2_up,
                    goods.plot2_flag2,
                    goods.build_density_down,
                    goods.build_density_flag,
                    goods.build_density_up,
                    goods.build_density_flag2,
                    goods.ild_height,
                    goods.green_ratio_down,
                    goods.green_ratio_flag,
                    goods.green_ratio_up,
                    goods.green_ratio_flag2,
                    goods.remark,
                    goods.other_condition,
                    goods.start_required,
                    goods.deposit_price,
                    goods.price_step,
                    goods.crj_yjk,
                    goods.allow_union,
                    goods.percent_price,
                    goods.develop
                ],
            )?;
        }
    }
    Ok(())
}

/**
 * 插入或更新区县信息
 * 
 * 根据canton_id判断是否已存在记录：
 * - 不存在则插入新记录
 * - 已存在且canton_name、organ_id或orgen_name有变化时，更新相应字段
 * 
 * @param conn 数据库连接
 * @param canton 区县信息对象
 * @return 成功时返回Ok(()), 失败时返回错误
 */
pub fn insert_or_update_canton(conn: &Connection, canton: &Canton) -> Result<()> {
    // 检查canton_id是否已存在
    let mut stmt = conn.prepare(
        "SELECT canton_name, organ_id, orgen_name FROM canton_table WHERE canton_id = ?"
    )?;
    
    match stmt.query_row([&canton.canton_id], |row| {
        let db_canton_name: Option<String> = row.get(0)?;
        let db_organ_id: Option<String> = row.get(1)?;
        let db_orgen_name: Option<String> = row.get(2)?;
        Ok((db_canton_name, db_organ_id, db_orgen_name))
    }) {
        Ok((db_canton_name, db_organ_id, db_orgen_name)) => {
            // 检查是否有字段发生变更
            if db_canton_name != canton.canton_name || 
               db_organ_id != canton.organ_id || 
               db_orgen_name != canton.orgen_name {
                // 有变更，执行更新操作
                conn.execute(
                    "UPDATE canton_table SET 
                        canton_name = ?, 
                        organ_id = ?, 
                        orgen_name = ? 
                     WHERE canton_id = ?",
                    params![
                        canton.canton_name, 
                        canton.organ_id, 
                        canton.orgen_name, 
                        &canton.canton_id
                    ],
                )?;
            }
            // 如果没有变更，则不执行任何操作
        },
        Err(_) => {
            // 不存在则插入新记录
            conn.execute(
                "INSERT INTO canton_table (canton_id, canton_name, organ_id, orgen_name) 
                 VALUES (?, ?, ?, ?)",
                params![
                    &canton.canton_id,
                    canton.canton_name,
                    canton.organ_id,
                    canton.orgen_name
                ],
            )?;
        }
    }
    
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::models::Canton;
    #[test]
    fn test_create_db() -> Result<(), Box<dyn std::error::Error>> {
        let conn = create_db()?;
        let mut stmt = conn.prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table'")?;
        let count: i64 = stmt.query_row([], |row| row.get(0))?;
        assert_eq!(count, 5); // 现在有5个表了
        Ok(())
    }
    
    #[test]
    fn test_insert_or_update_canton() -> Result<(), Box<dyn std::error::Error>> {
        let conn = create_db()?;
        
        // 1. 测试插入新记录
        let canton1 = Canton {
            id: None,
            canton_id: "1001".to_string(),
            canton_name: Some("历下区".to_string()),
            organ_id: Some("ORG001".to_string()),
            orgen_name: Some("历下区交易中心".to_string()),
        };
        insert_or_update_canton(&conn, &canton1)?;
        
        // 验证插入成功
        let mut stmt = conn.prepare("SELECT id, canton_name, organ_id, orgen_name FROM canton_table WHERE canton_id = ?")?;
        let row = stmt.query_row(["1001"], |row| {
            Ok((
                row.get::<_, u64>(0)?,
                row.get::<_, Option<String>>(1)?,
                row.get::<_, Option<String>>(2)?,
                row.get::<_, Option<String>>(3)?,
            ))
        })?;
        assert_eq!(row.1, Some("历下区".to_string()));
        assert_eq!(row.2, Some("ORG001".to_string()));
        assert_eq!(row.3, Some("历下区交易中心".to_string()));
        let original_id = row.0;
        
        // 2. 测试更新已存在的记录（有字段变更）
        let canton2 = Canton {
            id: None,
            canton_id: "1001".to_string(),
            canton_name: Some("历下区更新".to_string()), // 变更
            organ_id: Some("ORG001".to_string()),        // 不变
            orgen_name: Some("历下区交易中心更新".to_string()), // 变更
        };
        insert_or_update_canton(&conn, &canton2)?;
        
        // 验证更新成功，且id保持不变
        let row = stmt.query_row(["1001"], |row| {
            Ok((
                row.get::<_, u64>(0)?,
                row.get::<_, Option<String>>(1)?,
                row.get::<_, Option<String>>(2)?,
                row.get::<_, Option<String>>(3)?,
            ))
        })?;
        assert_eq!(row.0, original_id); // id保持不变
        assert_eq!(row.1, Some("历下区更新".to_string()));
        assert_eq!(row.2, Some("ORG001".to_string()));
        assert_eq!(row.3, Some("历下区交易中心更新".to_string()));
        
        // 3. 测试更新但无字段变更
        let canton3 = Canton {
            id: None,
            canton_id: "1001".to_string(),
            canton_name: Some("历下区更新".to_string()),
            organ_id: Some("ORG001".to_string()),
            orgen_name: Some("历下区交易中心更新".to_string()),
        };
        insert_or_update_canton(&conn, &canton3)?;
        
        // 验证记录没有变化，id仍然保持不变
        let row = stmt.query_row(["1001"], |row| {
            Ok((
                row.get::<_, u64>(0)?,
                row.get::<_, Option<String>>(1)?,
                row.get::<_, Option<String>>(2)?,
                row.get::<_, Option<String>>(3)?,
            ))
        })?;
        assert_eq!(row.0, original_id); // id仍然保持不变
        assert_eq!(row.1, Some("历下区更新".to_string()));
        assert_eq!(row.2, Some("ORG001".to_string()));
        assert_eq!(row.3, Some("历下区交易中心更新".to_string()));
        
        Ok(())
    }
}